import python_baway.exercise.mysql.oop.MysqlHelper as MysqlHelper
from python_baway.common.xdb import xdb_args
from python_baway.common.xcommon import xshow_table

xhelper = MysqlHelper.MysqlHelper(**xdb_args)

xsqls = (
    '''CREATE TABLE IF NOT EXISTS customer
(
	id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name varchar(20) NOT NULL DEFAULT 'Unknown'
)
CHARACTER SET = utf8
COLLATE = utf8_general_ci''',

    '''CREATE TABLE IF NOT EXISTS product
(
	id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name varchar(20) NOT NULL DEFAULT 'Unknown'
)
CHARACTER SET = utf8
COLLATE = utf8_general_ci''',

    '''CREATE TABLE IF NOT EXISTS supplier
(
	id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name varchar(20) NOT NULL DEFAULT 'Unknown'
)
CHARACTER SET = utf8
COLLATE = utf8_general_ci''',

    '''CREATE TABLE IF NOT EXISTS supplier_product
(
	id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	supplier_id INT(11) UNSIGNED NULL DEFAULT NULL,
	product_id INT(11) UNSIGNED NULL DEFAULT NULL,
	CONSTRAINT fk_supplier_id FOREIGN KEY (supplier_id) REFERENCES supplier (id) ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES product (id) ON DELETE RESTRICT ON UPDATE CASCADE,
	from_date DATETIME NULL,
	cached_to_date DATETIME NULL
)
CHARACTER SET = utf8
COLLATE = utf8_general_ci''',

'''CREATE TABLE IF NOT EXISTS `order`
(
	id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	customer_id INT(11) UNSIGNED NULL,
	`datetime` DATETIME NULL,
	CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customer (id) ON DELETE RESTRICT ON UPDATE CASCADE
)
CHARACTER SET = utf8
COLLATE = utf8_general_ci''',

'''CREATE TABLE IF NOT EXISTS order_item
(
	id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	order_id INT(11) UNSIGNED NULL,
	supplier_product_id INT(11) UNSIGNED NULL,
	CONSTRAINT fk_order_id FOREIGN KEY (order_id) REFERENCES `order` (id) ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT fk_supplier_product_id FOREIGN KEY (supplier_product_id) REFERENCES supplier_product (id) ON DELETE RESTRICT ON UPDATE CASCADE
)
CHARACTER SET = utf8
COLLATE = utf8_general_ci''',
)

for xsql in xsqls:
    xr = xhelper.exec(xsql)
    print(f'Affected rows: {xr}')

xr = xhelper.query('SELECT COUNT(*) FROM customer LIMIT 1')
if xr[0][0] == 0:
    xsql = '''
    INSERT INTO customer
    (name)
    VALUES
    ('皮Pi 大狗BigDog'),
    ('吴Wu 大棉BigSheep'),
    ('皮Pi 小猪SmallPig')
    '''
    xr = xhelper.exec(xsql)
    print(f'Affected rows: {xr}')

xr = xhelper.query('SELECT COUNT(*) FROM product LIMIT 1')
if xr[0][0] == 0:
    xsql = '''
    INSERT INTO product
    (name)
    VALUES
    ('糖Sugar'),
    ('自行车Bicycle'),
    ('别墅House')
    '''
    xr = xhelper.exec(xsql)
    print(f'Affected rows: {xr}')

xr = xhelper.query('SELECT COUNT(*) FROM supplier LIMIT 1')
if xr[0][0] == 0:
    xsql = '''
    INSERT INTO supplier
    (name)
    VALUES
    ('三菱'),
    ('Toyota'),
    ('Google')
    '''
    xr = xhelper.exec(xsql)
    print(f'Affected rows: {xr}')

xr = xhelper.query('SELECT COUNT(*) FROM supplier_product LIMIT 1')
if xr[0][0] == 0:
    xsql = '''
    INSERT INTO supplier_product
    (product_id, supplier_id, from_date, cached_to_date)
    VALUES
    (1, 3, '2011-10-05 00:00:00', '9999-12-31 23:59:59'),
    (2, 1, '2011-09-05 12:00:00', '2020-09-06 11:59:59'),
    (2, 2, '2020-09-06 12:00:00', '2021-02-13 08:00:00'),
    (3, 1, '1955-06-02 00:00:00', '9999-12-31 23:59:59')
    '''
    xr = xhelper.exec(xsql)
    print(f'Affected rows: {xr}')

xr = xhelper.query('SELECT COUNT(*) FROM `order` LIMIT 1')
if xr[0][0] == 0:
    xsql = '''
    INSERT INTO `order`
    (customer_id, datetime)
    VALUES
    (1, '2021-02-13 10:02:51'),
    (1, '2021-02-13 10:03:52'),
    (2, '2021-02-13 10:04:18'),
    (3, '2021-02-13 10:04:24')
    '''
    xr = xhelper.exec(xsql)
    print(f'Affected rows: {xr}')

xr = xhelper.query('SELECT COUNT(*) FROM `order_item` LIMIT 1')
if xr[0][0] == 0:
    xsql = '''
    INSERT INTO `order_item`
    (order_id, supplier_product_id)
    VALUES
    (1, 1),
    (1, 2),
    (2, 1),
    (2, 2),
    (3, 3),
    (3, 4),
    (4, 1),
    (4, 2),
    (4, 3),
    (4, 4)
    '''
    xr = xhelper.exec(xsql)
    print(f'Affected rows: {xr}')

xsql = '''\
SELECT o.id, o.datetime, c.`name`, p.`name`, s.`name`
FROM order_item oi
JOIN `order` o ON o.id = oi.order_id
JOIN supplier_product sp ON sp.id = oi.supplier_product_id
JOIN supplier s ON s.id = sp.supplier_id
JOIN product p ON p.id = sp.product_id
JOIN customer c ON c.id = o.customer_id
ORDER BY o.datetime'''
xr = xhelper.query(xsql)
xshow_table(xr)

xhelper.close()
